package com.lxxie;

import com.xielx.ris.statistic.db.DbUtil;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * @Description TODO
 * @Date 2021/11/30 15:16
 * @Created by xielx
 */
public class ImportJbtz {
    private NamedParameterJdbcTemplate jdbcTemplate;
    private String[] qy_code = new String[]{"000510077001001","000510077002001","000510077001002"};
    private String[] user = new String[]{"5100211125000836434934","5100211130000837102039","5100211130000837010927"};
    private Random rd = new Random();

    @Before
    public void init(){
        jdbcTemplate = new NamedParameterJdbcTemplate(DbUtil.getJdbcTemplate());
    }

//    insert into net_rural_jbtz_driver (BM, QY_CODE, XINGM, XINGB, SFZH, MOBILE, HUJD, CHANGQGZD, LIANXDZ, DOTIME, ISJSZ, ISSHANGB, ADDUSER, ADDTIME, EDITTIME, ISDEL, SFDM, GXSJ, ISSHENH, ISNONGJJSY, FIRST_CODE, SECOND_CODE, THIRD_CODE, FOURTH_CODE, SFZD)
    //values ('5100160825000001224753', '000510020007014007', '丁某文', '男', '51252719830424713X', '15284150783', '000510020007014007', '000510020007014007', '四川省宜宾县李场镇******', to_date('01-01-2016', 'dd-mm-yyyy'), '有', 9, '5100160809000000394840', to_date('25-08-2016 16:43:42', 'dd-mm-yyyy hh24:mi:ss'), to_date('29-08-2016 15:22:30', 'dd-mm-yyyy hh24:mi:ss'), 0, '5100', to_date('29-08-2016 15:22:30', 'dd-mm-yyyy hh24:mi:ss'), 9, 0, '000510', '000510020', '000510020007', '000510020007014', '0');

    @Test
    public void testDrv(){
        String insertSql = "insert into net_rural_jbtz_driver (BM, QY_CODE, XINGM, XINGB, SFZH, MOBILE, HUJD, CHANGQGZD, LIANXDZ, DOTIME, ISJSZ, ISSHANGB, " +
            "ADDUSER, ADDTIME, EDITTIME, ISDEL, SFDM, GXSJ, ISSHENH, ISNONGJJSY, FIRST_CODE, SECOND_CODE, THIRD_CODE, FOURTH_CODE, SFZD)"+
            "values(:BM,:QY_CODE,:XINGM,:XINGB,:SFZH,:MOBILE,:HUJD,:CHANGQGZD,:LIANXDZ,:DOTIME,'有',9,:ADDUSER,:ADDTIME,:EDITTIME,0,'5200'," +
            ":GXSJ,9,'0','000510','000510077',:THIRD_CODE,:FOURTH_CODE,:SFZD)";
        String selectSql = "select * from mpp_user_6b.drivinglicense";
        List<Map<String, Object>> list = jdbcTemplate.query(selectSql, new ColumnMapRowMapper());
        HashMap[] params = list.stream().map(temp -> {
            int index = rd.nextInt(qy_code.length);
            Map<String, Object> result = new HashMap<>();
            result.put("BM", "5100160" + temp.get("XH"));
            result.put("QY_CODE", qy_code[index]);
            result.put("XINGM", temp.get("XM"));
            result.put("XINGB", "1".equals(temp.get("XB")) ? "男" : "女");
            result.put("SFZH", temp.get("SFZMHM"));
            result.put("MOBILE", temp.get("SJHM"));
            result.put("HUJD", qy_code[index]);
            result.put("CHANGQGZD", qy_code[index]);
            result.put("LIANXDZ", temp.get("LXZSXXDZ"));
            result.put("DOTIME", temp.get("GXSJ"));
            result.put("ADDUSER", user[index]);
            result.put("ADDTIME", temp.get("YXQS"));
            result.put("EDITTIME", temp.get("YXQS"));
            result.put("GXSJ", temp.get("GXSJ"));
            result.put("THIRD_CODE", qy_code[index].substring(0, 12));
            result.put("FOURTH_CODE", qy_code[index].substring(0, 15));
            result.put("SFZD", rd.nextInt(101) > 80 ? "1" : "0");
            return result;
        }).toArray(HashMap[]::new);
        jdbcTemplate.batchUpdate(insertSql,params);
        jdbcTemplate.getJdbcOperations().execute("commit");
    }

    @Test
    public void testVeh(){
        String insertSql = "insert into net_rural_jbtz_car (BM, QY_CODE, CP, CHELLB, SHIYXZ, HUJD, ISKY, CHELSYR, MOBILE1, LIANXZZ, DOTIME, ISSHANGB, " +
            "ISSHENH, ADDUSER, ADDTIME, EDITTIME, ISDEL, SFDM, GXSJ, CQSZD, HPZL, FIRST_CODE, SECOND_CODE, THIRD_CODE, FOURTH_CODE, SFZD)"+
            "values(:BM,:QY_CODE,:CP,:CHELLB,:SHIYXZ,'01',0,:CHELSYR,:MOBILE1,:LIANXZZ,:DOTIME,9,9,:ADDUSER,:ADDTIME,:EDITTIME," +
            "0,'5100',:GXSJ,'01',:HPZL,'000510','000510077',:THIRD_CODE,:FOURTH_CODE,:SFZD)";
        String selectSql = "select * from mpp_user_6b.vehicle";
        List<Map<String, Object>> list = jdbcTemplate.query(selectSql, new ColumnMapRowMapper());
        HashMap[] params = list.stream().map(temp -> {
            int index = rd.nextInt(qy_code.length);
            Map<String, Object> result = new HashMap<>();
            result.put("BM", "51001608" + temp.get("XH"));
            result.put("QY_CODE", qy_code[index]);
            result.put("CP", temp.get("FZJG").toString().substring(0,1)+temp.get("HPHM"));
            String cllx = temp.get("CLLX").toString();
            String hpzl = temp.get("HPZL").toString();
            if ("K39K49".contains(cllx)) {
                result.put("CHELLB", "07");
            }else if ("M11M12M13M14M15".contains(cllx)) {
                result.put("CHELLB", "23");
            }else if ("M21M22".contains(cllx)) {
                result.put("CHELLB", "21");
            }else if ("N11".contains(cllx)) {
                result.put("CHELLB", "11");
            }else if ("T21T22".contains(cllx)) {
                result.put("CHELLB", "27");
            }else if ("T11".contains(cllx)) {
                result.put("CHELLB", "28");
            }else if ("0151".contains(hpzl)) {
                result.put("CHELLB", "16");
            }else {
                result.put("CHELLB", "08");
            }
            result.put("HPZL",hpzl);
            result.put("SHIYXZ",temp.get("SYXZ"));
            result.put("CHELSYR", temp.get("SYR"));
            result.put("MOBILE1", temp.get("SJHM"));
            result.put("LIANXZZ", temp.get("ZZXXDZ"));
            result.put("DOTIME", temp.get("GXSJ"));
            result.put("ADDUSER", user[index]);
            result.put("ADDTIME", temp.get("YXQS"));
            result.put("EDITTIME", temp.get("YXQS"));
            result.put("GXSJ", temp.get("GXRQ"));
            result.put("THIRD_CODE", qy_code[index].substring(0, 12));
            result.put("FOURTH_CODE", qy_code[index].substring(0, 15));
            result.put("SFZD", rd.nextInt(101) > 80 ? "1" : "0");
            return result;
        }).toArray(HashMap[]::new);
        jdbcTemplate.batchUpdate(insertSql,params);
        jdbcTemplate.getJdbcOperations().execute("commit");
    }
}
